﻿if object_id('fn_MapHTML_GpsVisitXmissionStructure') is not null
DROP FUNCTION fn_MapHTML_GpsVisitXmissionStructure

GO

CREATE FUNCTION [dbo].[fn_MapHTML_GpsVisitXmissionStructure]
(
	@GPSVisitPoleId int
)
RETURNS varchar(max)
AS
BEGIN
	declare @img xml
	set @img = '<img src="http://dev-poleposition.com/DEV/Icons/transparent_150.png" />'

	declare @html nvarchar(max)
	set @html = 
	(
		select
			'GPS XMission Visit' as 'TITLE'
			,Pole.Year
			,Pole.LVisit
			,CAST(Pole.Length as nvarchar) AS Length
			,Pole.LVisitor
			,species.Name as 'SpecieName'
			,classes.Name as 'ClassName'
			,treatments.Name as 'TreatmentName'
			,topTypes.Name as 'TopTypeName'

			-- visit images <IMAGE href="" src="" />
			,(select
				 replace(replace(replace(replace(Src.SettingValue, '{0}', Images.Vendor_Visit_XMissionStructures_ImageId), '{1}', 'GPSPoleXmission'), '{2}', 85), '{3}', 85) as '@src'
				--,replace(replace(replace(replace(Href.SettingValue, '{0}', Images.Vendor_Visit_XMissionStructures_ImageId), '{1}', 'GPSPoleXmission'), '{2}', 640), '{3}', 480) as '@href'
				,replace(Href.SettingValue, '{0}', replace(replace(replace(replace(Src.SettingValue, '{0}', Images.Vendor_Visit_XMissionStructures_ImageId), '{1}', 'GPSPoleXmission'), '{2}', 640), '{3}', 480)) as '@href'
				,Images.*
			from 
				tb_Vendor_Visit_XMissionStructures_Images as Images
			where 
				Images.Vendor_Visit_XMissionStructures_PoleFK = @GPSVisitPoleId
			for xml path ('IMAGE'), type) as '*'

		from 
			tb_Vendor_Visit_XMissionStructures_Poles as Pole
				LEFT JOIN tb_Settings as Href 
					ON Href.SettingId = 25
				LEFT JOIN tb_Settings as Src 
					ON Src.SettingId = 24
			LEFT JOIN tb_XMissionStructures_PoleSpecies species
				ON Pole.XMissionStructures_PoleSpeciesFk = species.XMissionStructures_PoleSpeciesId 
			LEFT JOIN tb_XMissionStructures_PoleClasses classes
				ON Pole.XMissionStructures_PoleClassFk = classes.XMissionStructures_PoleClassId 
			LEFT JOIN tb_XMissionStructures_PoleTreatments treatments
				ON Pole.XMissionStructures_PoleTreatmentFk = treatments.XMissionStructures_PoleTreatmentId 				
			LEFT JOIN tb_XMissionStructures_PoleTopTypes topTypes
				ON Pole.XMissionStructures_PoleTopTypeFk = topTypes.XMissionStructures_PoleTopTypeId
		where
			Pole.Vendor_Visit_XMissionStructures_PoleId = @GPSVisitPoleId
		for xml path ('VISIT')
)	
	return @html

END


